{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 利用SQL对淘宝天池数据进行分析\n",
    "## 数据来源及指标解释\n",
    "本数据来源于阿里云天池：https://tianchi.aliyun.com/dataset/dataDetail?dataId=649&userId=1\n",
    "具体字段含义如下：\n",
    "\n",
    "| 字段 | 含义 |   \n",
    "| :---: | :-----: |    \n",
    "| user_id | 客户ID |\n",
    "| item_id | 商品ID |\n",
    "| category_id| 商品类目 |\n",
    "| behavior | 行为类型: {pv:点击，fav:收藏，cart:加入购物车，buy:购买} |\n",
    "| timestamps| 时间戳|\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据理解及清洗\n",
    "\n",
    "首先我们从最粗浅的角度，理解这几个字段。用户ID可以帮助我们了解到整体用户数量，并可以结合购买行为用于计算复购率，结合购买总额计算客单价；商品ID和商品类目，可以帮助我们了解用户整体商品偏好，甚至可以利用apriori算法，发现潜在的购物规律；用户各种行为，则能让我们了解不同行为阶段的转化率，然后拿来跟其它同行做对比，发现问题所在或竞争优势；时间戳则可以转换为日期和时间，日期用来判断行业淡季旺季等整体情况，时间则可以用来判断每天哪些时间客流量大，广告收益高。以上就是对这些字段的初步理解。\n",
    "\n",
    "然后我们再来用python对数据分别进行如下处理：\n",
    "\n",
    "```python\n",
    "import pandas as pd\n",
    "from pandas import DataFrame \n",
    "from pandasql import sqldf\n",
    "data=pd.read_csv(r'E:\\学习资料\\数据分析\\UserBehavior.csv\\UserBehavior_1.csv')  #用pandas库读取数据\n",
    "data.columns=['user_id','Item_id','category_id','behavior','timestamps']    #由于原CSV表格没有列名，我们需要自行给它加上列名\n",
    "data['datetime']=pd.to_datetime(data['timestamps'],unit='s',utc=True).dt.tz_convert('Asia/Shanghai')  #将时间戳转换为时间\n",
    "data['date'],data['time']=data['datetime'].apply(lambda x: x.date()),data['datetime'].apply(lambda x: x.time())\n",
    "print(data.head())\n",
    "print(data.describe())  #显示数值字段描述统计信息，运行后发现没有空值\n",
    "print(data.describe(include=['O']))  #显示非数值字段描述统计信息，运行后发现没有空值\n",
    "print(data['date'].max(),data['date'].min())   #由于天池中数据日期为2017-11-25至2017-12-3，这步是为了确定有没有数据超出这个日期，如果有就进行删除\n",
    "print(data.dtypes)  #显示数据类型\n",
    "data.head()   #显示数据前五行\n",
    "pysqldf=lambda sql: sqldf(sql,globals())   #引入sqldf模块，使得可以直接用SQL查询对数据进行梳理\n",
    "sql1=\"SELECT DISTINCT * FROM data where date between '2017-11-25' and '2017-12-3'\"  #清除重复值和数据不在这个时间范围内的数据\n",
    "data=(pysqldf(sql1))   #执行SQL语句并赋值\n",
    "print(data.shape)      #查看数据结构\n",
    "```\n",
    "运行结果如下：\n",
    "\n",
    "![](./运行结果1.jpg)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据挖掘及分析\n",
    "\n",
    "### 漏斗分析\n",
    "我们先要从数据中提取到相应的pv、fav、cart、buy数量，然后计算相应转化比。\n",
    "```python\n",
    "from pyecharts import options as opts  #注意下载1.6.1版本的pyecharts\n",
    "from pyecharts.charts import Funnel   \n",
    "import numpy as np\n",
    "pyechart_data=list(data['behavior'].value_counts().values) #结合上面的代码，通过method获取到数据\n",
    "print(pyechart_data)  #结果是这个[3578660, 223014, 116284, 80007]，分别代表了相应的行为数量\n",
    "labels = [\"pv\", \"fav\", \"cart\", \"buy\"] #设置标签\n",
    "def funnel_base() -> Funnel:\n",
    "    c = (\n",
    "        Funnel()\n",
    "        .add(\"转化\", [list(z) for z in zip(labels, [3578660, 223014, 116284, 80007])])\n",
    "        .set_global_opts(title_opts=opts.TitleOpts(title=\"淘宝客户转化图\"))\n",
    "        .render_notebook()   ###只有在jupyter lab/notebook上显示才用加_notebook,否则不加，具体情况看官方文档。\n",
    "    )\n",
    "    return c\n",
    "funnel_base() #展示漏斗图\n",
    "pyechart_data=np.array(pyechart_data)\n",
    "pyechart_data=pyechart_data/max(pyechart_data)*100 #通过构建数组，快速计算百分比，如果数据量大的话，这种方法远比使用for i in list 要快\n",
    "```\n",
    "从中我们可以发现从pv到buy整体转化率为2.2%，需要获得100次点击才能出两单，此时就可以结合获客成本和每单平均收益，来判断是否需要进行广告投放等行为。\n",
    "上面的代码漏斗图如下：\n",
    "\n",
    "\n",
    "![](./淘宝客户转化图.png)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 流量分析\n",
    "接下来我们看一看，不同时段用户行为有什么不同。\n",
    "```python\n",
    "import pandas as pd\n",
    "from pandasql import sqldf\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "data=pd.read_csv('./final_userbehavior.csv')  #该数据为清洗后的数据集\n",
    "pysqldf=lambda sql: sqldf(sql,globals())\n",
    "sql1=\"SELECT count(behavior) AS num,strftime('%H',time) AS hour ,behavior FROM data GROUP BY hour,behavior\"  #注意这里使用的语法是SQLITE的语法\n",
    "active_period=(pysqldf(sql1))\n",
    "ax = sns.lineplot(x=\"hour\", y=\"num\",hue=\"behavior\" , data=data_pv)\n",
    "plt.show()\n",
    "```\n",
    "![](./活跃时段分布图.png)\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "从图中我们可以看到整个用户的活跃程度可以分为四个阶段：\n",
    ">1. 4:00-9:30的攀升期，此时用户渐渐从睡眠中醒来，但是还未到公司和学校进行工作和学习；\n",
    ">2. 9:30-18:00的稳定期，这个时间段是用户比较繁忙的阶段，所以整体没有较明显趋势，只有小幅波动；\n",
    ">3. 18:00-21:00的巅峰期，人们大抵在这个时间段进行休闲娱乐，购物行为达到高潮；\n",
    ">4. 21:00-4:00的低迷期，夜生活进入尾声，大多人都进入睡梦当中。\n",
    "\n",
    "那么我们能从这张图表中获得什么呢？首先我们可以知道哪个时间段进行广告投入效果最好，但有时我们也可以考虑性价比，选择流量稳定期。同时我们也知道在什么时间要加大运营力度，在流量高峰期客服就应该打起精神，合理安排排班，否则部分客户在询问之后得不到即时回复就会立刻跳转，从而会使得购买率降低。在商品推荐方面，我们也要学会在流量高峰期推荐那些受顾客喜欢利润高的商品，或者是对那些库存过多的商品进行捆绑销售。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 商品复购率及平均复购周期分析\n",
    "#### 复购率\n",
    "```python\n",
    "sql1=\"SELECT COUNT(num) FROM (SELECT count(user_id) AS num FROM data  WHERE behavior='buy' GROUP BY user_id HAVING num>=2)\" #计算有重复购买行为的客户数量\n",
    "print(pysqldf(sql1))  #输出是17431\n",
    "sql2=\"SELECT COUNT(*) FROM data where behavior='buy'\"  #计算有进行购买的客户数量，输出是80007\n",
    "\n",
    "```\n",
    "所以最终商品复购率为21.78%，由于我们的数据时段是2017-11-25至2017-12-3，在这9天内有重复购买的人占购买人数的21.78%，是高是低要结合其它电商平台进行分析，同时还要结合相应商品进行分析，我们这里算的是总的复购率，如果是那种一次购买能使用3个月的牙刷，那这种复购率就相当高，如果是那种时效性强的水果生鲜，这种复购率就稍显偏低，毕竟水果人们大概两天左右买一次。\n",
    "\n",
    "#### 平均复购周期\n",
    "```python\n",
    "sql1=\"SELECT user_id,datetime,row_number()  OVER(PARTITION BY user_id ORDER BY datetime ASC ) as rnk    \\\n",
    "FROM data WHERE user_id IN (SELECT  user_id  FROM data  WHERE behavior='buy' GROUP BY user_id HAVING count(user_id)>=2)\"  #筛选出有复购的客户数据\n",
    "data2=pysqldf(sql1)\n",
    "sql2=\"SELECT SUM((julianday(a.datetime)-julianday(b.datetime))*24) FROM \\\n",
    "(SELECT user_id,datetime from data2 where rnk=2) AS a  JOIN  (SELECT  user_id,datetime from data2 where rnk=1) AS b ON a.user_id=b.user_id \" \n",
    "#注意SQLITE中没有datediff()函数，所以只能这样计算第一次购买与第二次购买之间的时间间隔之和\n",
    "print(pysqldf(sql2))  #总间隔时间为95808.139444小时\n",
    "```\n",
    "\n",
    "所以平均复购周期为5.5个小时，这意味着有复购行为的客户每隔5.5小时就会在淘宝上进行购物。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 用户画像搭建\n",
    "根据产品人梁宁的理论，可以把用户分成三类分别是“小明”、“笨笨”、“小闲”。小明是指那些具有明确购物需求的人，比如男生大多是小明买完内衣内裤，不再进行任何留恋；笨笨是指有消费欲望，但不知道要买什么的人，就像是许多女生热爱逛小红书一样；小闲则是纯粹为了娱乐的人群，女生逛街并不一定要买什么，但逛街可以带给她们快乐，消磨时间；对于那些未进行任何购买行为的用户，我们给他的画像是无效用户或者是低价值用户。虽然我们的数据字段中没有浏览时间，但是浏览时间跟浏览点击量成正比，我们可以用count(pv)/count(buy)来判断一个用户它是直奔主题，还是一直在闲逛。\n",
    "\n",
    "```python\n",
    "import pandas as pd\n",
    "from pandasql import sqldf\n",
    "import numpy as np\n",
    "data=pd.read_csv('./final_userbehavior.csv')\n",
    "pysqldf=lambda sql: sqldf(sql,globals())\n",
    "sql1=\"SELECT  a.user_id,CASE   \\\n",
    "WHEN num_of_buy IS NULL THEN   0 \\\n",
    "WHEN num_of_buy>0  THEN  num_of_pv/num_of_buy \\\n",
    "END  AS  用户分类  \\\n",
    "FROM (SELECT user_id, COUNT(behavior) as num_of_pv FROM data WHERE behavior='pv' GROUP BY user_id ) AS  a  \\\n",
    "LEFT JOIN  (SELECT user_id, COUNT(behavior) as num_of_buy FROM data WHERE behavior='buy' GROUP BY user_id  ) AS  b  ON a.user_id=b.user_id ORDER BY 用户分类\"\n",
    "data2=pysqldf(sql1)\n",
    "print(data2.describe())\n",
    "print(data2.shape)\n",
    "sql2=\"SELECT count(*) from data2 where 用户分类>0\"\n",
    "print(pysqldf(sql2))\n",
    "sql3=\"SELECT * from data2 LIMIT 1 OFFSET 21297\"\n",
    "print(pysqldf(sql3))\n",
    "sql4='SELECT user_id, CASE WHEN 用户分类=0 THEN \"无效用户\"  WHEN 用户分类>0 AND 用户分类<=21 THEN \"小明用户\" WHEN  用户分类<=45 THEN \"笨笨用户\" WHEN 用户分类<=649 THEN \"小闲用户\" END  \\\n",
    "AS 用户分类 FROM data2  '\n",
    "data3=pysqldf(sql4)\n",
    "print(data3.head())\n",
    "# data3.to_csv('用户评级.csv') 如需保存可以加上这句\n",
    "```\n",
    "![](./运行结果2.jpg)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "针对不同画像的用户，我们可以采用不同营销方法。比如针对那些无效用户，我们可以尝试分析是什么阻止他们进行购物，可能的原因就有暂时手头没钱，这个时候我们就可以提供分期付款的方式来刺激消费，另一种可能是他们是新用户，但付款流程过于繁琐，阻止他们进行消费；对于“小明”用户，我们就在商品推荐时尽量推荐那些广受好评性价比高的商品，以此建立信任，让他在下次购物时还选择淘宝这个平台；针对“笨笨”用户，我们可以用Apriori算法进行智能推荐，使得能更大限度地猜测出用户喜好；对于“小闲”用户，我们就着力挖掘社交功能，使得他发现有趣产品后，分享给自己的朋友，使得她朋友来平台进行购买。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 结论与建议\n",
    "1. 我们能从漏斗图中看到，从用户点击到喜欢这个环节，转化率下降最为明显，也就是说很多商品推荐并不符合用户口味，如果能通过对算法的优化，使得用户能看到大量喜欢的商品，那么最终成功率就能得到显著提高，这也就要求对客户进行更细化的分类，需要对用户数据进行更广泛更精确的收集。\n",
    "2. 我们发现晚6点至9点是流量高峰期，此时我们就可以通过短信等方式，告知客户店铺已经上新，这样成本低效率高。并且结合成本考虑，流量高峰期不意味着淘宝店主一定要进行推荐位、广告购买，要结合性价比考虑，当边际成本大于边际报酬时，也就是获客成本大于新客户带来的净利润总和时，就应该停止相应开销。\n",
    "3. 9天之内进行复购的用户达到21.78%，平均复购周期为5.5小时，可以通过建立微信群的方式，给老客内部优惠，以此来稳固住已有买家。\n",
    "4. 我们将用户总共分成四类，而针对不同用户，我们分别提出分期付款、减少付款流程、利用算法进行智能推荐、增加社交属性等建议。"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
